# -*- coding: UTF-8 -*-
import copy
import ctypes
import datetime
import json
import math
import multiprocessing
import os
import sys
import time
from itertools import groupby
from multiprocessing import Pool, Process
from operator import itemgetter

from tqdm import tqdm

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.utils import CommonUtils
from src.db.CcxDataCenterDb import CcxDataCenter
from src.db.FCDB import FCDB
from src.db.ProdDb import ProdDb
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger


class Db:
    """
    公共资源
    """

    def __init__(self, localDb, dataCenterDb, fcdb):
        self.localDb = localDb
        self.dataCenterDb = dataCenterDb
        self.fcdb = fcdb


class DbLoader:
    def __init__(self, logger):
        self.logger = logger
        self.localDb = ProdDb()

    def loadProcessList(self):
        sql = """
        SELECT year from index_process_year
            where TYPE =0 order by YEAR  desc
        """
        result = list(self.localDb.Query(sql))
        result = [int(item['year']) for item in result]
        return result

    # 抽取待更新主体名单
    def loadEnterpriseList(self):
        sql = "select id, credit_code,enterprise_name, comp_code,ent_type,ent_type_sub from ent_enterprise where 1=1 "
        creditCode = CommonUtils.isCommandParam(sys.argv, "creditCode")
        if creditCode['exist']:
            self.logger.warn("指定企业")
            temp = ','.join('"{0}"'.format(x) for x in creditCode['value'].split(','))
            sql = sql + " and credit_code in (" + temp + ")"

        entType = CommonUtils.isCommandParam(sys.argv, "entType")
        if entType['exist']:
            self.logger.warn("指定企业类型运算")
            sql = sql + " and ent_type ='" + entType['value'] + "'"

        tradeType = CommonUtils.isCommandParam(sys.argv, "tradeType")
        if tradeType['exist']:
            self.logger.warn("指定企业类型运算")
            sql = sql + " and trade_type ='" + tradeType['value'] + "'"

        return self.localDb.Query(sql)

    def loadSettings(self):
        sql = " SELECT *  from fin_index_calc_settings"
        indexCode = CommonUtils.isCommandParam(sys.argv, "indexCode")
        if indexCode['exist']:
            self.logger.warn("指定科目运算")
            temp = ','.join('"{0}"'.format(x) for x in indexCode['value'].split(','))
            sql = sql + " where code in (" + temp + ")"
        return self.localDb.Query(sql)


class CalcIndicator:
    def __init__(self):
        self.logger = Logger()
        self.fcdb = FCDB()
        self.dataCenterDb = CcxDataCenter()
        self.localDb = ProdDb()
        self.size = 1000

    def __loadDataCenterPlatformAnnaData(self, creditCode, years: list):
        sql = """
            SELECT
        a.credit_code,b.second_code as 'code',b.value,replace(a.`year`,"Q4","" ) as year
        FROM
        city_investment_companies_annual_data a
        LEFT JOIN city_investment_companies_annual_data_detail  b ON a.id = b.foreign_id
        WHERE
        a.YEAR in %s and a.credit_code =%s
         and b.second_code='CTND001001' and value <> 'N/A' and a.release_status=1 and a.ver=0
        
        """

        newYear = copy.deepcopy(years)
        newYear = {str(k) + 'Q4' for k in newYear}
        return self.dataCenterDb.Query(sql, (newYear, creditCode))

    def __loadLocalIndustryOperData(self, creditCode, years: list):
        sql = """
            SELECT id, credit_code, `year`, conent_text, update_time
            FROM fin_enterprise_oper_data
            where credit_code = %s and year in %s
        """
        result = self.localDb.Query(sql, (creditCode, years))
        if len(result) > 0:
            result.sort(key=itemgetter("year"))
            return {k: {dd["year"]: dd["conent_text"] for dd in d} for k, d in
                    groupby(result, key=itemgetter('year'))}

    def __loadDataCenterGuaranteeAnn(self, creditCode, years: list):
        sql = """
         SELECT  da.`year`,d.code ,d.value  
                        FROM
                        guarantee_companies_annual_data_detail d
                        LEFT JOIN guarantee_companies_annual_data da ON d.foreign_id  = da.id
                        WHERE
                        da.`year` in %s and da.credit_code  = %s
                        and da.release_status =1 and da.ver =0 and da.status =1 and d.code is not null
        """
        result = self.dataCenterDb.Query(sql, (years, creditCode));
        if len(result) > 0:
            result.sort(key=itemgetter("year"))
            return {k: {dd["code"]: dd["value"] for dd in d} for k, d in
                    groupby(result, key=itemgetter('year'))}
        return

    def __loadDataCenterSecuritiesAnn(self, creditCode, years: list):
        sql = """
            SELECT
            a.credit_code,
            a.YEAR as year,
            b.CODE as code,
            c.NAME,
            b.`value`
            FROM
            securities_enterprise_annual_data a
            LEFT JOIN securities_enterprise_annual_data_detail b ON a.id = b.annual_data_id
            LEFT JOIN base_field_metadata c ON c.`code` = b.CODE
            WHERE
            a.YEAR in %s and a.credit_code = %s
            order by b.value*1 desc
        """
        result = self.dataCenterDb.Query(sql, (years, creditCode));
        if len(result) > 0:
            result.sort(key=itemgetter("year"))
            return {k: {dd["code"]: dd["value"] for dd in d} for k, d in
                    groupby(result, key=itemgetter('year'))}
        return

    def __loadFcTqFinFnfcloanc(self, compCode, years: list):
        sql = """
            select AMOUNT,ITEMCODE,REPORTDATE,replace(REPORTDATE,"1231","") as reportYearCloanc  from TQ_FIN_FNFCLOANC where COMPCODE =%s
            and ITEMCODE  in (2,3,4,5) and REPORTDATE in %s
        """
        newYears = copy.deepcopy(years)
        newYears = {str(v) + "1231" for v in newYears}
        return self.fcdb.Query(sql, (compCode, newYears))

    def __loadFcTqProseCompare(self, compCode, years: list):
        sql = """
            SELECT ID, COMPCODE, PUBLISHDATE, REPORTYEAR, REPORTDATETYPE, BEGINDATE, ENDDATE, ACCSTACODE, ISAUDIT, NCAP, NCAPTONA, NCAPTODEBT, NATODEBT, EQYSECPROTONCAP, NA, ENTPOUNDAGEINC, NCAPTOSUMRISKRES, CALERATIO, LSILCRTO, NESTFURATE, EQNSECPROTONCAP, RISKRESESUM, CNCAP, SNCAP, TOTBASHASSET, FINMRGTONCAP, CUR, ISVALID, TMSTAMP, ENTRYDATE, ENTRYTIME, DATA_DOWNLOAD_TIME
            FROM fcdb.tq_fin_prosecompare
            where COMPCODE= %s and REPORTYEAR in %s and REPORTDATETYPE=4

        """
        fcData = self.fcdb.Query(sql, (compCode, years))
        return fcData

    def __loadFcTqFinProbankCompare(self, compCode, years: list):
        sql = """
        select
                ID,
                COMPCODE,
                PUBLISHDATE,
                COMPTYPE,
                REPORTYEAR,
                REPORTDATETYPE,
                CUR,
                BEGINDATE,
                ENDDATE,
                ACCSTACODE,
                ISAUDIT,
                BADLOANRTO,
                NPLPCRT,
                LOPRRATIO,
                NIS,
                NIM,
                COSTTOINC,
                NONINTINCTR,
                ROE,
                NPTOTA,
                LOANTODEPOSRT,
                CURRENTRT,
                LSILCRTO,
                NESTFURATIO,
                CALERATIO,
                CRESRT,
                CAR,
                T1CAR,
                CORET1CAR,
                SINGBIGLOANRT,
                MAXTENLOANRT,
                BADLOAN,
                TDEP,
                TLOAN,
                INTBEARASS,
                INTBEARDEBT,
                NCAP,
                NT1CAP,
                NCORET1CAP,
                ISVALID,
                TMSTAMP,
                ENTRYDATE,
                ENTRYTIME,
                DATA_DOWNLOAD_TIME,
                HIQULIASSAR,
                LIMATCHR
            from
                tq_fin_probankcompare
                where COMPCODE= %s and REPORTYEAR in %s and REPORTDATETYPE=4
            
        """
        fcData = self.fcdb.Query(sql, (compCode, years))
        return fcData

    def __loadReportData__(self, creditCode: str, years: list) -> dict:

        minYear = min(years)
        newYears = copy.deepcopy(years)
        newYears.append(minYear - 1)
        newYears.append(minYear - 2)

        sql = """
            SELECT r.credit_code,r.fin_year, d.local_code,DATE_FORMAT(r.report_date,'%%Y-%%m-%%d') as report_date, d.value FROM fin_report  r
            LEFT JOIN fin_report_data d on r.sync_id = d.report_id
            WHERE r.credit_code = %s
            AND r.caliber = 1
            AND r.fin_year in %s
            and d.local_code is not null
            ORDER BY r.fin_year
        """
        reportData = self.dataCenterDb.Query(sql, (creditCode, newYears))
        if len(reportData) == 0:
            return None
        reportData.sort(key=itemgetter("report_date"))
        return {k: {dd["local_code"]: dd["value"] for dd in d} for k, d in
                groupby(reportData, key=itemgetter('report_date'))}

    @staticmethod
    def convertFloat(s):
        if s is None or s == '':
            return None
        try:
            return float(s)
        except Exception as e:
            return None

    def __getCurrentReportData__(self, dicReportData: dict, year: str, baseData: dict, reportType: int):
        if dicReportData is None:
            baseData.update({"isExistReport": 0})
            return
        baseData.update({"isExistReport": 1})
        currentYear = {}
        t0 = {}
        t1 = {}
        t2 = {}
        reportTypeStr = "-03-31" if reportType == 0 else "-06-30" if reportType == 1 else "-09-30" if reportType == 2 else "-12-31" if reportType == 3 else "未知"
        yearT0 = f"{year}{reportTypeStr}"
        yearT1 = f"{int(year) - 1}{reportTypeStr}"
        yearT2 = f"{int(year) - 2}{reportTypeStr}"
        if yearT0 in dicReportData.keys():
            t0Data = dicReportData[yearT0]
            currentYear = {k: self.convertFloat(v) for k, v in t0Data.items()}
            t0 = {k + "_t0": self.convertFloat(v) for k, v in t0Data.items()}
        if yearT1 in dicReportData.keys():
            t1 = dicReportData[yearT1]
            t1 = {k + "_t1": self.convertFloat(v) for k, v in t1.items()}
        if yearT2 in dicReportData.keys():
            t2 = dicReportData[yearT2]
            t2 = {k + "_t2": self.convertFloat(v) for k, v in t2.items()}

        currentYear.update(t0)
        currentYear.update(t1)
        currentYear.update(t2)
        baseData.update(currentYear)
        return baseData

    def __deleteOldData__(self, creditCode: str, year: int, settings: list):
        sql = """
            DELETE FROM fin_index
            WHERE credit_code= %s and year =%s  and index_code in %s
        """
        setting = list({d['code'] for d in settings})
        self.localDb.ExecNoneQuery(sql, (creditCode, year, setting))

    def __save__(self, updateDatas: list, creditCode, reportType, year):
        sql = """
        replace INTO fin_index
        (credit_code, `year`,report_type,index_code, index_value)
        VALUES(%(creditCode)s, %(year)s, %(reportType)s,%(code)s,%(value)s)  
        """

        sql_update = """
        update  fin_index
        set index_value =%s where
          credit_code=%s and year=%s and report_type=%s and  index_code=%s
        """

        sql_by_condition = """
            SELECT * from fin_index
            where credit_code =%s and year=%s and report_type=%s
        """

        db = self.localDb.Query(sql_by_condition, (creditCode, year, reportType))

        if db is None or len(db) == 0:
            self.localDb.ExecMany(sql, updateDatas)
        else:
            # 循环找到重复值
            dbGroupingByCode = {v["index_code"]: v['index_value'] for v in db}
            updateList = []
            for newItem in updateDatas:
                code_ = newItem["code"]
                value_ = newItem["value"]
                # 数据库中没有同年份同CODE同财报类型的数据新增
                if code_ not in dbGroupingByCode.keys():
                    updateList.append(newItem)
                # 与数据库历史数据不一致，更新操作
                elif dbGroupingByCode[code_] != (
                        None if value_ is None else str(value_)):
                    updateList.append(newItem)
            self.localDb.ExecMany(sql, updateList)

    def __calc__(self, settings, currentReportData, creditCode, year, entTypeSub, reportType):
        result = []

        # 此科目为季报时 忽略
        ignoreKeys = ["ZB_0123",
                      "ZB_0311",
                      "ZB_0312",
                      "ZB_0313",
                      "ZB_0320",
                      "ZB_0321",
                      "ZB_0604",
                      "ZB_0605",
                      "ZB_0606",
                      "ZB_0607",
                      "ZB_0608",
                      "ZB_0609",
                      "ZB_0610",
                      "ZB_0611",
                      "ZB_0612",
                      "ZB_0613",
                      "ZB_0614",
                      "ZB_0615",
                      "ZB_0616",
                      "ZB_0617",
                      "ZB_0618",
                      "ZB_0621",
                      "ZB_0622"]
        if entTypeSub == 'T00801':
            entTypeSub = 'T003'
        if entTypeSub == 'T00802':
            entTypeSub = 'T004'
        if entTypeSub == 'T00101':
            entTypeSub = 'T001'
        if entTypeSub == 'T00201':
            entTypeSub = 'T002'
        if entTypeSub == 'T00501':
            entTypeSub = 'T006'
        for setting in settings:
            if setting[entTypeSub] == 0:
                continue
            code = setting['code']
            # 未知 = -1, 一季报 = 0, 半年报 = 1, 三季报 = 2, 年报 = 3(以数据中心为准)
            if code in ignoreKeys and reportType != 3:
                continue
            formula = setting["formula"]
            item = {}
            item["creditCode"] = creditCode
            item["year"] = year
            # 0 年报
            item["reportType"] = reportType
            item["code"] = code
            calResult = None
            try:
                # 是财务指标且财报没有
                if formula.__contains__("zx_") and currentReportData['isExistReport'] == 0:
                    calResult = None
                else:
                    calResult = eval(formula, currentReportData)
                    # calResult = None if calResult is None else "%.2f" % calResult
            except Exception as e:
                # self.logger.error(f"指标：{setting['code']}, 公式：, {formula}, 原因: {e}")
                pass

            finally:
                item["value"] = None if calResult is None else str(calResult)
                result.append(item)

        return result

    def __getCurrentPlatfomrAnnaData(self, platformAnnaData, year, baseData):
        if platformAnnaData is None:
            return None
        try:
            result = list(filter(lambda x: x['year'] == str(year), platformAnnaData))[0]
            ret = {result["code"]: float(result['value'])}
            baseData.update(ret)
        except Exception as e:
            # self.logger.error(e)
            pass

    def __getCurrentLocalIndustryOperData(self, industryData, year, baseData):
        if industryData is None:
            return None
        try:
            result = industryData[year][year]
            ret = {v["code"]: v['value'] for v in json.loads(result)}
            baseData.update(ret)
        except Exception as e:
            # self.logger.error(e)
            pass

    def __getCurrentDcGuaranteeAnna(self, guaranteeAnnaData, year, baseData):
        try:
            baseData.update(guaranteeAnnaData[str(year)])
        except Exception as e:
            # self.logger.error(e)
            pass

    @staticmethod
    def __getCurrentFcFinProseCompare(currentFinData, year, baseData):
        if currentFinData is None:
            return None
        for item in currentFinData:
            if item['REPORTYEAR'] == str(year):
                baseData.update(item)
        return None

    # 数据中心证券公司数据
    def __getCurrentDcSecuritiesAnna(self, securitiesAnnaData, year, baseData):
        try:
            baseData.update(securitiesAnnaData[year])
        except Exception as e:
            # self.logger.error(e)
            pass

    @staticmethod
    def __getCurrentFcLoanc__(currentBankData, year, baseData):
        if currentBankData is None:
            return None
        for item in currentBankData:
            if item['REPORTDATE'] == (str(year) + '1231'):
                key = ('AMOUNT' + item['ITEMCODE']);
                baseData.update({key: item['AMOUNT']})
        return None

    # 财汇银行数据
    @staticmethod
    def __getCurrentFcBankData__(currentBankData, year, baseData):
        if currentBankData is None:
            return None
        for item in currentBankData:
            if item['REPORTYEAR'] == str(year):
                baseData.update(item)
        return None

    def calcEnterprise(self, settings, enterprise, years):
        try:

            creditCode = enterprise["credit_code"]
            compCode = enterprise["comp_code"]
            entType = enterprise["ent_type"]
            entTypeSub = enterprise["ent_type_sub"]
            enterpriseName = enterprise["enterprise_name"]
            # 财报为必抽数据
            dicReports = self.__loadReportData__(creditCode, years)

            # 如果是银行，抽取财汇银行年度数据
            FcBankData = None
            FcFinProseCompareData = None
            FcBankLoanc = None
            # 证券公司年度数据-数据中心
            DcSecuritiesAnnData = None
            # 担保公司年度数据 - 数据中心
            DcGuaranteeAnnData = None
            # 产业经营数据
            LocalFinEntOperData = None
            # 城投年度数据
            DcPlatformAnnaData = None

            # 补丁：财务分析，企业类型为“其他”,当产业类型处理

            if entTypeSub == 'T00501' or entTypeSub == 'T00801':
                # if entType == 'T005':
                # entType = 'T001'
                # if entType == 'T003':
                FcBankData = self.__loadFcTqFinProbankCompare(compCode, years)
                FcBankLoanc = self.__loadFcTqFinFnfcloanc(compCode, years)
            # 如果是券商，抽取券商年度数据
            if entTypeSub == 'T00802':
                # if entType == 'T004':
                DcSecuritiesAnnData = self.__loadDataCenterSecuritiesAnn(creditCode, years)
                FcFinProseCompareData = self.__loadFcTqProseCompare(compCode, years)

            # 如果是担保，抽取担保年度数据
            if entTypeSub == 'T00803':
                # if entType == 'T006':
                DcGuaranteeAnnData = self.__loadDataCenterGuaranteeAnn(creditCode, years)

            # 如果是产业，抽取产业经营数据
            # if entType == 'T001':
            if entTypeSub == 'T00101':
                LocalFinEntOperData = self.__loadLocalIndustryOperData(creditCode, years)

            # 如果是城投，抽取城投年度数据
            if entTypeSub == 'T00201':
                DcPlatformAnnaData = self.__loadDataCenterPlatformAnnaData(creditCode, years)

            for year in years:
                # self.logger.debug(f"开始运算:{enterpriseName}, {year}年, 信用代码：{creditCode}")
                # 跑批基础数据集
                # 未知 = -1, 一季报 = 0, 半年报 = 1, 三季报 = 2, 年报 = 3(以数据中心为准)
                for reportType in [0, 1, 2, 3]:
                    baseData = {}
                    # todo
                    # 判断财报类型，如果是年度，“月份”参数为12,现在暂时为12，后续补充季报再调整
                    if reportType == 3:
                        baseData.update({"月份": 12})
                    baseData.update({"year": year})
                    # 组装财报数据
                    self.__getCurrentReportData__(dicReportData=dicReports, year=year, baseData=baseData,
                                                  reportType=reportType)
                    if reportType == 3:
                        # 组装财汇银行年度数据
                        self.__getCurrentFcBankData__(FcBankData, year, baseData)
                        # 组装财汇报表附注数据
                        self.__getCurrentFcLoanc__(FcBankLoanc, year, baseData)
                        # 组装数据中心证券公司年度数据
                        self.__getCurrentDcSecuritiesAnna(DcSecuritiesAnnData, year, baseData)
                        self.__getCurrentFcFinProseCompare(FcFinProseCompareData, year, baseData)

                        # 组装担保公司年度数据
                        self.__getCurrentDcGuaranteeAnna(DcGuaranteeAnnData, year, baseData)

                        # 组装产业经营数据
                        self.__getCurrentLocalIndustryOperData(LocalFinEntOperData, year, baseData)

                        # 组装城投年度数据
                        self.__getCurrentPlatfomrAnnaData(DcPlatformAnnaData, year, baseData)
                    calcResult = self.__calc__(settings, baseData, creditCode, year, entTypeSub, reportType)
                    # self.__deleteOldData__(creditCode=creditCode, year=year, settings=settings)
                    self.__save__(calcResult, creditCode, reportType, year)

        except Exception as ec:
            self.logger.error(ec)
            pass


def calcEnterpriseList(workId, settings, enterpriseList, years, shareVars, locker):
    sync = CalcIndicator()
    try:
        for i in range(len(enterpriseList)):
            sync.calcEnterprise(settings, enterpriseList[i], years)

            # 加锁
            locker.acquire()
            shareVars["current"] = shareVars["current"] + 1
            locker.release()

    except Exception as ex:
        sync.logger.error("calcEnterpriseList error", ex)


# 多进程运算
def processing():
    logger = Logger()
    dbLoader = DbLoader(logger)

    # 加载设置
    settings = dbLoader.loadSettings()

    # 加载待计算企业列表
    enterpriseList = dbLoader.loadEnterpriseList()

    yearByCommand = CommonUtils.isCommandParam(sys.argv, "year")
    if yearByCommand['exist']:
        years = list({int(dd) for dd in yearByCommand["value"].split(",")})
    else:
        years = dbLoader.loadProcessList()

    logger.info("本次运算年份:%s" % years)

    if CommonUtils.isCommandParam(sys.argv, "multiprocess")['exist']:
        cores = multiprocessing.cpu_count()
        logger.info(f"并行计算 Processes：{cores}")

        pool = Pool(processes=cores)
        locker = multiprocessing.Manager().Lock()

        # 进度数据
        shareVars = multiprocessing.Manager().dict()
        shareVars["current"] = 0

        # 数据按cores数量分组处理
        size = math.ceil(len(enterpriseList) / cores)
        enterpriseLL = [enterpriseList[i:i + size] for i in range(0, len(enterpriseList), size)]
        for workId in range(len(enterpriseLL)):
            pool.apply_async(func=calcEnterpriseList,
                             args=(workId, settings, enterpriseLL[workId], years, shareVars, locker),
                             error_callback=lambda x: logger.error(x))

        pool.close()
        # 延迟1秒启动进度条
        time.sleep(1)
        pg = tqdm(total=len(enterpriseList), desc="processing", position=0, file=sys.stdout)
        # 热等待
        pgValue = 0
        total = len(enterpriseList)
        while True:
            tmp = shareVars["current"]
            diff = tmp - pgValue
            if diff > 0:
                pg.update(diff)
            pgValue = tmp
            if pgValue >= total:
                break

        pool.join()

    else:
        logger.info(f"顺序计算")
        sync = CalcIndicator()
        for enterprise in tqdm(enterpriseList, desc="processing", position=0, file=sys.stdout):
            sync.calcEnterprise(settings, enterprise, years)


def __main__():
    logger = Logger()

    cleanScreen()

    t = time.perf_counter()
    logger.info("计算指标财务指标开始===================================")

    # windows下multiprocess必须在__name__== "__main__"定义的函数执行
    processing()

    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('指标财务指标计算结束 %s 总耗时: %d分%.3f秒' % (datetime.datetime.now(), m, s))


# 程序入口
if __name__ == "__main__":
    try:
        __main__()
    except Exception as e:
        print(e)
